﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class CTHD_DAO
    {
        public static DataTable hienthicthd()
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            cnn.Open();
            string sql = "select * from CTHD";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            cnn.Close();
            return dtb;
        }
        //Tìm kiếm 
        public static DataTable timkiem(string Mahd)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "select * from CTHD where MaHD = @mahd";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@mahd", SqlDbType.NVarChar, 10);
            cmd.Parameters["@mahd"].Value = Mahd;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }

        public static DataTable timkiemNC(string mahd, string masp, string from, string to, string soluong)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "select * from CTHD where MaHD like '%" + mahd + "%' and MaSP like'%" + masp + "%' and Soluong like'%" + soluong + "%' and (ThanhTien between '" + from + "'and'" + to + "')";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }

        public static void ThemCTHD(CTHD_DTO cthd)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "insert into CTHD values(@mahd,@ma,@soluong,@thanhtien) end";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@mahd", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@soluong", SqlDbType.Int);
            cmd.Parameters.Add("@thanhtien", SqlDbType.Int);

            cmd.Parameters["@mahd"].Value = cthd.mahd;
            cmd.Parameters["@ma"].Value = cthd.masp;
            cmd.Parameters["@soluong"].Value = cthd.soluong;
            cmd.Parameters["@thanhtien"].Value = cthd.thanhtien;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void SuaCTHD(CTHD_DTO cthd, string mahd, string macu)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "declare @dg int  set @dg = (select Dongia from SANPHAM where MaSP = @ma) update CTHD set MaHD=@mahd,MaSP=@ma,SoLuong=@soluong,ThanhTien=@soluong*@dg where MaHD = @mahd MaSP='" + macu + "'";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@mahd", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@soluong", SqlDbType.Int);
            cmd.Parameters.Add("@thanhtien", SqlDbType.Int);


            cmd.Parameters["@mahd"].Value = cthd.mahd;
            cmd.Parameters["@ma"].Value = cthd.masp;
            cmd.Parameters["@soluong"].Value = cthd.soluong;
            cmd.Parameters["@thanhtien"].Value = cthd.thanhtien;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void XoaCTHD(CTHD_DTO cthd, string MaHD, string MaSP)
        {
            SqlConnection cnn = sqlConnectionData.Ketnoi();
            string sql = "delete SANPHAM where MaHD = @mahd and MaSP=@ma";
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.Parameters.Add("@mahd", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ma", SqlDbType.NVarChar, 10);
            cmd.Parameters["@mahd"].Value = cthd.mahd;
            cmd.Parameters["@ma"].Value = cthd.masp;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}
